/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package connection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Song;

/**
 *
 * @author Student
 */
public class DaoTransaction {

    public boolean addTransactionSong(String songId, String userId) throws SQLException {
        Connector connect = new Connector();
        Connection conn = connect.getConn();


        PreparedStatement ps = conn.prepareStatement("INSERT INTO ROOT.TRANSACTION_LOG ("
                + " USER_ID, "
                + " TRAN_TYPE_ID, "
                + " DATE_CREATED, "
                + " ALBUM_ID, "
                + " SONG_ID, "
                + " reviewed)  "
                + " VALUES "
                + " (?, 1,  CURRENT_DATE, 0, ?, false)");

        ps.setString(1, userId);
        ps.setString(2, songId);



        return ps.executeUpdate() != 0 ? true : false;
    }

    public boolean addTransactionAlbum(String albumId, String userId) throws SQLException {
        boolean success = false;
        Connector connect = new Connector();
        Connection conn = connect.getConn();


        PreparedStatement ps = conn.prepareStatement("INSERT INTO ROOT.TRANSACTION_LOG ("
                + " USER_ID, "
                + " TRAN_TYPE_ID, "
                + " DATE_CREATED, "
                + " ALBUM_ID, "
                + " SONG_ID, "
                + " reviewed )  "
                + " VALUES "
                + " (?, 2,  CURRENT_DATE, ?, 0, false)");

        ps.setString(1, userId);
        ps.setString(2, albumId);



        success = ps.executeUpdate() != 0 ? true : false;




        DaoSong dao = new DaoSong();
        dao.setAlbumId(albumId);
        ArrayList<Song> songlist = dao.getSongInAlbum();



        ps = conn.prepareStatement("INSERT INTO ROOT.TRANSACTION_LOG ("
                + " USER_ID, "
                + " TRAN_TYPE_ID, "
                + " DATE_CREATED, "
                + " ALBUM_ID, "
                + " SONG_ID, "
                + " reviewed)  "
                + " VALUES "
                + " (?, 1,  CURRENT_DATE, 0, ?, false)");

        ps.setString(1, userId);






        for (Song song : songlist) {
            ps.setInt(2, song.getSongId());
            ps.executeUpdate();
        }



        return success;
    }

    public ResultSet getToysRs() {
        ResultSet rs = null;
        try {



            Connector jdbc = new Connector();
            Connection conn = jdbc.getConn();

            String query = "select  tl.tran_id, au.username, tran_type, tl.date_created, s.title, s.price from transaction_log tl inner join transaction_type tt on tt.tran_type_id = tl.tran_type_id inner join account_user au on tl.user_id = au.user_id inner join song s on s.song_id = tl.song_id order by tl.tran_id";
            PreparedStatement ps = conn.prepareStatement(query);

            rs = ps.executeQuery();



        } catch (SQLException ex) {
            Logger.getLogger(DaoTransaction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }
}
